package dao;

import entity.Department;
import entity.School;
import util.JdbcHelper;

import java.sql.*;

public class DepartmentDaoImpl implements DepartmentDaoInterface {
    static private DepartmentDaoInterface departmentDaoImpl = null;
    public DepartmentDaoImpl(){}

    public static DepartmentDaoInterface getInstance(){
        if (departmentDaoImpl == null) {
            departmentDaoImpl = new DepartmentDaoImpl();
            System.out.println("Create DepartmentDaoImpl Object");
        }
        return departmentDaoImpl;
    }
    // ID 查询
    @Override
    public Department find(Long id) throws SQLException {

        Connection conn = JdbcHelper.getConn();

        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Department WHERE id = ?");
        pstmt.setLong(1, id);

        ResultSet resultSet = pstmt.executeQuery();

        Department desiredDepartment = null;
        if (resultSet.next()) {
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            Long schoolId = resultSet.getLong("school_id");
            School school = SchoolDaoImpl.getInstance().find(schoolId);
            desiredDepartment = new Department(id, description, no, remarks, school);
        }

        return desiredDepartment;
    }

    // 增
    @Override
    public void add(Department department) throws SQLException {
        Connection conn = JdbcHelper.getConn();
        String sql= "INSERT INTO Department(description, no, remarks, school_id) VALUES(?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, department.getDescription());
        pstmt.setString(2, department.getNo());
        pstmt.setString(3, department.getRemarks());
        pstmt.setLong(4, department.getSchool().getId());
        pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
    }
    // 改
    @Override
    public boolean update(Connection connection, Department department) throws SQLException {
        String sql = "UPDATE Department SET description = ?, no = ?, remarks = ?, school_id = ? WHERE id = ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, department.getDescription());
        pstmt.setString(2, department.getNo());
        pstmt.setString(3, department.getRemarks());
        pstmt.setLong(4, department.getSchool().getId());
        pstmt.setLong(5, department.getId());
        int rows = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rows > 0;
    }
    // 删
    @Override
    public boolean delete(Integer id) throws SQLException {
        Connection conn = JdbcHelper.getConn();
        String sql = "DELETE FROM Department WHERE id =?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setLong(1, id);
        int rows = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rows > 0;
    }
}
